FIXED TABLE FULL
FIXED TABLE shows access to one of the dynamic performance tables known as X$ tables
Detailed Description​
FIXED TABLE (FULL) shows access to one of the dynamic performance tables known as X$ tables. These are in-memory structures rather than normal SQL tables and are maintained by the Oracle server. Direct access to the X$ tables is strongly advised against. They will however, show up in explain plans. Their contents are usually exposed through the V$... and GV$... set of views.
Performance of these objects can suffer if the optimizer is not aware of the actual statistics for them as unlike normal tables, predefined values are used rather than dynamic sampling if statistics are missing.
On versions older than 12c use the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure to update them.
Although newer versions include fixed tables in the scheduled statistics jobs, it is recommended by Oracle that fixed object statistics are gathered whilst a representative workload is being run.
Additional Links​
- Oracle Tuning Guide - Gathering Statistics for Fixed Objects
- Oracle Magazine - How to read an execution plan
- Oracle Tuning Guide - Explaining and Displaying Execution Plans
- Oracle Tuning Guide - Comparing Execution Plans Tutorial
Search online​
If this article doesn't have the information you need you can try searching online. Remember, you can contribute suggestions to this page.